Using an Excel Template to Import Data in maestro*

Objective

During the maestro* implementation or when a good amount of data needs to be entered in the software, manually entering data in a repetitive manner can become tedious (i.e. information about various customers). That is why maestro* is able to import data from Excel files. Depending on the data type, different file templates must be used.

There are two types of Excel import files in maestro*:

  • General Import - simple and similar, available in various maestro* options
  • Specific Import - specific to the option in which the data import is performed

The objective of this How To is to explain how to use the various Excel templates for the data import in maestro*.

 

We strongly recommend to first perform the data import in your test company.

 

Prerequisite

  • Microsoft Excel

 

Summary

 

Steps

General Import

The General Import is the most common. It is available in many maestro* options. There is no specific Excel template to use when performing this kind of import and the window is nearly the same in all cases. The main difference is the information.

 

The General Import tool is available in many options, such as: Customer Management, Supplier Management, Employee Management, etc.

General Import templates are quite simple; each column represents a field in the option. The first line of the grid must contain the field names, and all data to import must be entered in the following lines.

 

It is not necessary to have a column for each field in the option; only for the fields for which data needs to be imported.

Performing the Import

  1. Click on the Tools button of the toolbar at the top of the option.
  2. Click on the importation option in the scroll-down menu (Import, Import an Excel File, etc.). The Import Data window appears.
  3. To select the Excel file, click on the button made up of three dots "…", to the right of the Excel Data File field. A file selection window is displayed.
  4. Select the desired file and click on Open. The column names are displayed in the Imported Fields section.
  5. In order to link the Excel file columns to the option's fields, it is important to correctly enter which import field corresponds to each field in maestro*:

 

The Customer Management option is used as example.
  1. In the big table, select a field by clicking on the corresponding box in the Imported Fields column.
  2. Then, in the Imported Fields section, to the right of the table, double clic on the Excel column name that corresponds to the selected field.

  1. The selected field from the Imported Fields section should be displayed in the Imported Fields column of the big table.

  1. Repeat steps 5a. to 5c. for each field that needs to be imported.
  1. Once all fields have been selected, click the Transfer icon. This step may take a couple minutes, depending on the number of lines in the Excel file.
  2. Once the data import is over, a message will display how many lines have been imported in maestro*. Click Ok.
  3. Click Quit to close the import window.
  4. The data entered in the Excel file can now be found in maestro*.
Options Tab

It is possible to choose between three import options in the general import window. It is important to clearly distinguish them in order to avoid duplicates or involuntary deletions.

Option

Description

Allow overwrite of existing records

When checked, this option allows the data from the import file to replace the preexisting data.

For example, when we already have a customer list, but we want to update and replace the phone numbers using an Excel file.

NOTE: This option is only available if it has been authorized in the Security Management option.

Import all or nothing

When checked, this option allows the termination of an incomplete file import.

For example, if one line on 100 has an error, no lines will be imported.

Import only existing records

When checked, this option allows the addition of information in already existing maestro* records.

For example, if we already have a customer list in maestro* and we wish to add some customers' phone numbers without adding new customers during import.

 

Specific Import

The Specific Import is unique to the maestro* option in which it is found and a specif template for each of those options is necessary to perform the data import in maestro*.

 

The specific import appears in various1 options but only the following will be covered in this document: Enter a Sale, Enter a Requisition, Enter Hours, Project Management, and Annual Budget.

Enter Hours

 

maestro* > Time Management > Project Time > Processing > Enter Hours

 

The goal of importing hours is to import those related to the payroll for one or many employees.

  1. In the Enter Hours window, click on the Import Hours from Excel icon.
  2. The Import Hours from Excel window opens and displays the import grid. A file selection window is also displayed.
  3. If your Excel file has already been completed, simply select it, click on the Open button, and proceed to step 7. If it has not been completed, and to display the Excel template to use, click on Cancel. The file selection window will close.
  4. Back in the Import Hours from Excel window, it is possible to directly enter the information in the import grid. Otherwise, it is also possible to transfer this grid to Excel to make sure the imported file meets maestro*'s requirements:
  1. Click on the Create a template file icon.
  2. An Excel file opens. It can be completed, saved, and then imported in maestro*.

You can find an editable copie of this Excel document HERE.

 

The following fields are mandatory:

  • Date (Column A)

NOTE: All date data in the Excel file must match the maestro* date format.

  • Project-# (Column G)
  • Activity (Column H)
  • Group (Column I)
  • Hours (Column T)

It will be impossible to apply the changes brought upon by the data import if these fields are incomplete.

 

Attention! The Company column only appears in multidimensional mode. If you do not work in multidimensional, please ignore it.

For more information about these fields, please refer to the Enter Hours option's Online Help (F1).
  1. Once the Excel file has been completed, click on the New icon in the Import Hours from Excel window. A file selection window appears.
  2. Select the file and click on Open.
  3. Click on the Validate the Table icon to validate the imported data.

 

Attention! If columns are framed in red in the import grid, there are mistakes in the Excel file. In order to avoid the importation of skewed data, it is recommended to verify all information entered in the latter.

  1. Click on Apply to complete the data import.

  1. Click Save and Quit.

 

Enter a Requisition

 

maestro* > Purchasing > Purchasing > Procurement > Enter a Requisition

 

The importation of requisition data is different than other import types because its import window is more complex than a simple breakdown grid. It looks a lot like a new requisition entry, with fields to be completed in the top part of the window and a breakdown type grid at the bottom. A more complex Excel file is therefore required to proceed with the data import.

  1. In the Enter a Requisition window, click on the Import an Excel File icon.
  2. The Import requisitions from an Excel file window appears and displays the import grid as well as the fields of the Detail tab. A file selection window also appears.
  3. If your Excel file has already been completed, you simply need to select it, click on the Open button, and proceed to step #7. If it has not been completed, click on Cancel. The file selection window will close.
  4. Complete the Excel document and perform the importation afterwards. To ensure that no problem occurs during the data import, it is important to use the following Excel file template:

 

The Excel file template is not available in maestro*. It is therefore important to use the one put at your disposal in this document.

You can find an editable copie of this Excel document HERE.

 

The following fields are mandatory:

Detail (Line *, Column A & B)
  • Project (Line 1)
  • Requester (Line 2)
  • Requisition Date (Line 3)
  • Delivery Date (Line 4)
  • Delivery Location (Line 5)
  • Instruction 1 (Line 6)
  • Instruction 2 (Line 7)
Breakdown Grid (Line 9, Column *)
  • Inventory Code (Column A)
  • Unit (Column B)
  • UOM (Column C)
  • Quantity (Column D)
  • Description (Column F)
  • Activity (Column G)
  • Group (Column H)
  • Date (Column I)

NOTE: All date data in the Excel file must match the maestro* date format.

  • Supplier (Column N)

It will be impossible to apply the changes brought upon by the data import if these fields are incomplete.

 

Attention! The Company column only appears in multidimensional mode. If you do not work in multidimensional, please ignore it.

For more information about these fields, please refer to the Enter a Requisition option's Online Help (F1).

 

Attention! If some columns in the breakdown grid are highlighted in red, there are mistakes in the Excel file. In order to be able to proceed with the data import, it is mandatory to modify the entered information.

  1. Once the Excel file has been completed, click on the New icon in the Import requisitions from an Excel window. A file selection window appears.
  2. Select the file and click on Open.
  3. Click on the Validate the Table icon.

  1. Click on Apply in order to complete the data import.

  1. Click Save and Quit.

 

Enter a Sale

 

maestro* > Invoicing > Invoicing > Processing > Enter a Sale

 

The goal of the enter a sale importation is to import one or many invoice(s) in maestro* from an Excel file.

What makes the data import in the Enter a Sale option so particular is the possibility to import a single or various invoices at once.

Single Invoice Import
  1. In the Enter a Sale window, click on the Import icon, or on the downward pointing arrow to its right.
  2. Select Import an Excel File.
  3. A file selection window appears; there is no maestro* import window for a single invoice.

It is therefore necessary to have an already completed Excel file to proceed with the data import. Your document should look like the following example:

 

The Excel file template is not available in maestro*. It is therefore important to use the one put at your disposal in this document.

You can find an editable copie of this Excel document HERE.

 

The following fields are mandatory:

Detail (Line *, Column A & B)
  • Customer (Line 4)
  • Project (Line 24)

NOTE: Lines 10 to 14 and 36 to 47 are hidden in order to lighten the table.

Breakdown (Line 49, Column *)
  • Account (Column C)
  • Amount (Column I)

NOTE: Lines 53 to 63 are hidden in order to lighten the table.

NOTE: All date data in the Excel file must match the maestro* date format.

It will be impossible to apply the changes brought upon by the data import if these fields are incomplete.

 

Attention! The Company column only appears in multidimensional mode. If you do not work in multidimensional, please ignore it.

For more information about these fields, please refer to the Enter a Sale option's Online Help (F1).
  1. Select the import file and click on Open. A message will appear if there are any errors.

 

It is possible to modify the fields in maestro* if that is the case.
  1. The file selection window closes and the data import is completed in the respective fields.

  1. Click on Save and Quit.
Multiple Invoice Import
  1. In the Enter a Sale window, click on the downward pointing arrow to the left of the Import icon.
  2. Select Batch Excel Invoice Import.
  3. The Batch Excel Invoice Import window appears and displays the breakdown grids for the Invoices and Breakdown tabs. A file selection window also appears.

  1. If your Excel file has already been completed, you must simply select it, click on the Open button, and then proceed to step #7. If it has yet been completed, click on Cancel. The file selection window will close.
  2. Complete the Excel document and then perform the import. In order to have no problem during the data import, it is important to use the following Excel template:

 

The Excel file template is not available in maestro*. It is therefore important to use the one put at your disposal in this document.

You can find an editable copie of this Excel document HERE.

 

The following fields are mandatory:

  • Line # (Column A)

NOTE: This column is used to enter the line number of the Breakdown grid in maestro*, where the information from the Excel file will appear. Line 0 is used to enter the data in the different columns of the Excel file's Header section. The following lines are used to enter the information belonging in the Breakdown grid.

  • Customer Code (Column B)
  • Project (Column F)
  • Activity (Column AX)

NOTE: The following columns have been hidden in order to lighten the table: C, E, I to R, T to AA, AF to AO, AQ to AV, AZ, BA, BB, BD, BF, BG, and BJ to BO.

NOTE: All date data in the Excel file must match the maestro* date format.

It will be impossible to apply the changes brought upon by the data import if these fields are incomplete.

 

Attention! The Company column only appears in multidimensional mode. If you do not work in multidimensional, please ignore it.

For more information about these fields, please refer to the Enter a Sale option's Online Help (F1).
  1. Once the Excel file complete, click on the New icon in the Batch Excel Invoice Import window. A file selection window appears.
  2. Select the file and click on Open.
  3. The file selection window closes and the data import is performed (the bigger the file, the longer this operation will be).

  1. Click on Apply to complete the data import.
  2. The different imported invoices can be found in the Summary of non-transferred transactions section of the Enter a Sale window.

Projects

 

maestro* > Projects > Maintenance > Project > Project Management

 

The project import is actually the import of a project's activities and budgets. It makes it possible to update or create various activities, and to assign them quantities and budgets.

  1. In the Project Management window, select a project in the Number field, towards the top of the window.
  2. Once the project has been selected, click on the Activities and Budgets tab.

 

It is impossible to access the Excel import window if the Activities and budgets tab is not open since the import is for activities and not new projects.

  1. Click on the downward pointing arrow to the right of the Import icon. Select Import an Excel File.
  2. The Import activities and budgets from a standard format file window opens, followed by a file selection window.

  1. If you have already completed the Excel file, you need only select it, click on the Open button, and proceed to step #8. If it has yet been completed, click on Cancel. The file selection window will close.
  1. Complete the Excel document. In order to avoir any problem during the data import, it is important to use the following Excel file:

You can find an editable copie of this Excel document HERE.

 

You can also get the Excel file template by following these next steps:

  1. Make a right click anywhere in the grid.
  2. In the scrolldown menu, click on Transfer to Excel.

 

The only mandatory fields for this kind of data import are the Project Code (Column A) and the Activity Code (Column B). For the Project Code, one must only enter the same project code as was entered in step 1.

It is still recommended to use the given template to ensure every field is displayed in the right columns.

NOTE: Columns AA to AD represent check boxes. If the box needs to be checked, enter 1 instead of 0.

The default value is 0.

NOTE: All date data in the Excel file must match the maestro* date format.

 

Attention! The Company column only appears in multidimensional mode. If you do not work in multidimensional, please ignore it.

For more information about these fields, please refer to the Project Management option's Online Help (F1).
  1. Once the Excel file has been completed, click on the New icon in the Import activities and budgets from a standard format file window. A file selection window appears.
  2. Once the import of the Excel file is done, select one of the three Existing Budgets options:
  • All budgets for all companies and activities will be initialized and the values in the file will be imported
  • The values in the file will be added to existing budgets
  • The values in the file will replace existing budgets
  1. Click on the Validate the table icon. A confirmation will let you know if the information entered in the table is valid. If some fields are found to be invalid, it is possible to manually modify them in the import window.

  1. Click on the Apply icon to complete the data import to the Project Management option.
  2. The different imported activities can be found under the Activities and Budgets tab, in a table towards the top-left of the window.

Annual Budgets

 

maestro* > Financial Management > Budget > Enter Annual Budgets

 

The import of annual budgets allows to enter, using a single import, all account budgets, per period, for a year in the Enter Annual Budgets option.

 

Warning! Please note that the import of an annual budget will overwrite all other budget data for that year.

 

  1. In the Enter Annual Budgets window, click on the Import an Excel File icon.
  2. The Import an Excel File window appears and displays the importation grid. A file selection window also appears.
  3. If you have already completed the Excel file, you need only select it, click on the Open button, and proceed to step #7. If it has yet been completed, click on Cancel. The file selection window will close.
  4. Complete the Excel document. In order to avoid any problem during the data import, it is important to use the following Excel file:

 

The Excel file template is not available in maestro*. It is therefore important to use the one put at your disposal in this document.

You can find an modifiable copy of this Excel document HERE.

 

The following field is required:

  • Account No.

If no information is entered in the Account No. field, it will simply be impossible to proceed with the annual budget import.

The following fields are not mandatory, but must still be filled in if you do not wish to import a 0.00 budget for all of the periods:

  • All of the Budget Amount - Per. # fields.

NOTE: If this field is left empty, the imported value will be zero.

For more information about these fields, please refer to the Enter Annual Budgets option's Online Help (F1).
  1. Once the Excel file has been completed, click on the New icon in the Import an Excel File window. A file selection window appears.
  2. Select the file and click on Open.
  3. Make sure the date range in the Budget Year field is accurate before going forward.
  4. Click on the Validate the Table icon to make sure the data to import is correct.

 

Warning! If some of the fields in the columns are highlighted in red, this means that there is an error in the entered data.

You must rectify this mistake before being to able to continue the import.

NOTE: It is possible to make the modifications directly in the import grid.

  1. Click on Apply to complete the data import.
  2. A confirmation message will be displayed, warning you that the budget you are trying to import will overwrite all of the current year's budget data. Click on Yes to proceed with the annual budget import.
  3. Click on Quit.

  1. Once back in the Enter Annual Budgets window, click on Save to save the annual budget. Click on Quit.

 

Last modification: November 12, 2024